# -*- coding: utf-8 -*-
"""
Created on Wed Jul 11 14:28:03 2018

@author: xuchao
"""

#%%
import pandas as pd
import numpy as np
import pymysql


sql = """
    SELECT
        t.*, YEAR (t.`放款时间`) 'YEAR',
        QUARTER (t.`放款时间`) 'QUARTER',
        MONTH (t.`放款时间`) 'MONTH',
        WEEK (t.`放款时间`) 'WEEK'
    FROM
        qy_datasys.repay_ment t;
"""
conn = pymysql.connect(host='localhost',user='root',password='123456',port=3306,charset='UTF8')
df = pd.read_sql(sql,conn)
conn.close()

a1 = {}
a2 = {}
a3 = {}

for i in df.index:
    a1[i] = str(df['YEAR'].iloc[i])+'-Q'+str(df['QUARTER'].iloc[i])
    a2[i] = str(df['YEAR'].iloc[i])+'-M'+str(df['MONTH'].iloc[i])
    a3[i] = str(df['YEAR'].iloc[i])+'-W'+str(df['WEEK'].iloc[i])
df['统计季度'] = pd.Series(a1)
df['统计月份'] = pd.Series(a2)
df['统计周'] = pd.Series(a3)
    
def cal_overdue_rate(df):
    "人数"
    df_subset1 = df.pivot_table(index='逾期状态',columns='期数',values='客户姓名',aggfunc=len)
    "比率"
    df_subset2 = df_subset1.apply(lambda s : s/s.sum())
    "最终的逾期率"
    df_subset3 = pd.DataFrame(df_subset2.sum(axis=1)/df_subset2.shape[1]*12,columns=['rate'])
    print(df_subset1)
    print(df_subset2)
    print(df_subset3)
    
    return [df_subset1,df_subset2,df_subset3]

dataList = cal_overdue_rate(df)
dataList[2]